
let path = require('path')
import { v4 as uuidv4 } from 'uuid';
const knex = require('knex')({
  client: 'sqlite3',
  connection: {
    filename:  path.join(path.resolve(), `/static/data.db`),
  },
  useNullAsDefault: true
})

// Create a table
knex.schema
  .raw('CREATE TABLE gateway (id varchar(50) NOT NULL PRIMARY KEY, ip varchar(20), nm varchar(50), sn varchar(20), type varchar(20), protocol varchar(20), ip varchar(20), user varchar(20), pwd varchar(20), qos varchar(20), sname varchar(20), sid varchar(20), port varchar(20))')
  .raw('CREATE TABLE chanel (id varchar(50) NOT NULL PRIMARY KEY,  type varchar(20), protocol varchar(20), nb varchar(20), spd varchar(20), dbs varchar(20), sbs varchar(20), py varchar(20), tot varchar(20), parent_id varchar(50), FOREIGN KEY(parent_id) REFERENCES gateway(id) ON DELETE CASCADE )')
  .raw('CREATE TABLE device (id varchar(50) NOT NULL PRIMARY KEY, dnm varchar(20),  addr varchar(20), shortb varchar(20), intb varchar(20), doubleb varchar(20), strb varchar(20), parent_id varchar(50), FOREIGN KEY(parent_id) REFERENCES chanel(id) ON DELETE CASCADE )')
  .raw('CREATE TABLE tag (id varchar(50) NOT NULL PRIMARY KEY, gw_id varchar(50), nnm varchar(20), ch varchar(20), cd varchar(20), rsa varchar(20), rn varchar(20), dt varchar(20), rt varchar(20), rw varchar(20),  parent_id varchar(50), FOREIGN KEY(parent_id) REFERENCES device(id) ON DELETE CASCADE )')
  .raw('CREATE TABLE code (id varchar(50) NOT NULL PRIMARY KEY, group_name varchar(20), code varchar(10), name varchar(20))')
  .raw('CREATE TABLE user_tag  (id varchar(50) NOT NULL PRIMARY KEY, user_id varchar(50), tag_name varchar(50), tag_id varchar(50), FOREIGN KEY(tag_id) REFERENCES tag(id) ON DELETE CASCADE )')
  .raw('CREATE TABLE product_info  (id varchar(50) NOT NULL PRIMARY KEY, company varchar(20), phone varchar(20), addr varchar(20), ip varchar(20),  port varchar(20))')
  // // // Query both of the rows.
  .then(() => {
    knex('product_info')
    .insert([
      {id: uuidv4(), company: '中安智联未来', phone: "", addr: '郑州市总部企业基地1期11号楼2楼', ip: "192.168.1.1", port: "8980"},
    ])
  })
  .then(() => 
    knex('code')
    .insert([
      {id: uuidv4(), group_name: 'shortb', code:1, name: 'AB'}, //2字节
      {id: uuidv4(), group_name: 'shortb', code:2, name: 'BA'},
      {id: uuidv4(), group_name: 'intb', code:1, name: 'AB CD'}, //4字节
      {id: uuidv4(), group_name: 'intb', code:2, name: 'BA DC'},
      {id: uuidv4(), group_name: 'intb', code:1, name: 'CD AB'},
      {id: uuidv4(), group_name: 'intb', code:2, name: 'DC BA'},
      {id: uuidv4(), group_name: 'doubleb', code:1, name: 'AB CD EF GH'}, //8字节
      {id: uuidv4(), group_name: 'doubleb', code:2, name: 'BA DC FE HG'},
      {id: uuidv4(), group_name: 'doubleb', code:2, name: 'GH EF CD AB'},
      {id: uuidv4(), group_name: 'doubleb', code:2, name: 'HG FE DC BA'},
      {id: uuidv4(), group_name: 'strb', code:1, name: 'Little'}, //字符串
      {id: uuidv4(), group_name: 'strb', code:2, name: 'Big'},
      {id: uuidv4(), group_name: 'gateway_type', code:1, name: '网口'}, //传输端类型
      {id: uuidv4(), group_name: 'gateway_type', code:3, name: '串口'},
      {id: uuidv4(), group_name: 'gateway_type', code:2, name: '4G'},
      {id: uuidv4(), group_name: 'gateway_protocol', code:1, name: 'mqtt'}, //传输端协议
      {id: uuidv4(), group_name: 'gateway_protocol', code:2, name: 'opcua'},
      {id: uuidv4(), group_name: 'gateway_protocol', code:3, name: 'bacnet'},
      {id: uuidv4(), group_name: 'qos', code:1, name: 'qos0'},
      {id: uuidv4(), group_name: 'qos', code:2, name: 'qos1'},
      {id: uuidv4(), group_name: 'qos', code:3, name: 'qos2'},
      {id: uuidv4(), group_name: 'chanel_protocol', code:1, name: 'modbus'}, //采集端协议
      {id: uuidv4(), group_name: 'chanel_type', code:1, name: 'serial'}, //采集端类型
      {id: uuidv4(), group_name: 'chanel_type', code:2, name: 'net'},
      {id: uuidv4(), group_name: 'nb', code:1, name: 'com1'}, //通道编号
      {id: uuidv4(), group_name: 'nb', code:2, name: 'com2'},
      {id: uuidv4(), group_name: 'nb', code:3, name: 'com3'},
      {id: uuidv4(), group_name: 'nb', code:4, name: 'com4'},
      {id: uuidv4(), group_name: 'spd', code:1, name: 600}, //波特率
      {id: uuidv4(), group_name: 'spd', code:2, name: 1200},
      {id: uuidv4(), group_name: 'spd', code:3, name: 2400},
      {id: uuidv4(), group_name: 'spd', code:4, name: 4800},
      {id: uuidv4(), group_name: 'spd', code:5, name: 9600},
      {id: uuidv4(), group_name: 'spd', code:6, name: 19200},
      {id: uuidv4(), group_name: 'spd', code:7, name: 38400},
      {id: uuidv4(), group_name: 'spd', code:8, name: 57600},
      {id: uuidv4(), group_name: 'spd', code:9, name: 115200},
      {id: uuidv4(), group_name: 'dbs', code:1, name: 7}, //数据位
      {id: uuidv4(), group_name: 'dbs', code:2, name: 8},
      {id: uuidv4(), group_name: 'sbs', code:1, name: 1}, //停止位
      {id: uuidv4(), group_name: 'sbs', code:2, name: 2},
      {id: uuidv4(), group_name: 'py', code:1, name: 'N'}, //校验位
      {id: uuidv4(), group_name: 'py', code:2, name: 'O'},
      {id: uuidv4(), group_name: 'py', code:3, name: 'E'},
      {id: uuidv4(), group_name: 'cd', code:1, name: '01'}, //功能吗
      {id: uuidv4(), group_name: 'cd', code:2, name: '02'},
      {id: uuidv4(), group_name: 'cd', code:3, name: '03'},
      {id: uuidv4(), group_name: 'cd', code:4, name: '04'},
      {id: uuidv4(), group_name: 'cd', code:5, name: '05'},
      {id: uuidv4(), group_name: 'cd', code:6, name: '06'},
      {id: uuidv4(), group_name: 'cd', code:15, name: '15'},
      {id: uuidv4(), group_name: 'cd', code:16, name: '16'},
      {id: uuidv4(), group_name: 'dt', code:1, name: 'bool'},
      {id: uuidv4(), group_name: 'dt', code:2, name: '_bit'},
      {id: uuidv4(), group_name: 'dt', code:3, name: 'U8'},
      {id: uuidv4(), group_name: 'dt', code:4, name: 'S8'},
      {id: uuidv4(), group_name: 'dt', code:5, name: 'U16'},
      {id: uuidv4(), group_name: 'dt', code:6, name: 'S16'},
      {id: uuidv4(), group_name: 'dt', code:7, name: 'U32'},
      {id: uuidv4(), group_name: 'dt', code:8, name: 'S32'},
      {id: uuidv4(), group_name: 'dt', code:9, name: 'String'},
      {id: uuidv4(), group_name: 'dt', code:10, name: 'float'},
      {id: uuidv4(), group_name: 'dt', code:11, name: 'double'},
      {id: uuidv4(), group_name: 'rw', code:0, name: 'ro'},
      {id: uuidv4(), group_name: 'rw', code:1, name: 'wo'},

    ])
  )
  .catch(err=>{
    console.log(err)
  })
  
  // // map over the results
  // .then(rows =>
  //   rows.map(row => {
  //     console.log(row)
  //   })
  // )

  // Finally, add a .catch handler for the promise chain
  .catch(e => {
    console.error(e);
  });

